package dispatchers;

import java.sql.*;
import objects.*;
import util.*;
import java.util.*;
import java.io.*;

public class DBStatisticDispatcher{
	private DBManager dbManager;
	private String gameTableName;
	private String userTableName;
	
	public DBStatisticDispatcher(DBManager dbManager, String gameTableName, String userTableName){
		this.dbManager = dbManager;
		this.gameTableName = gameTableName;
		this.userTableName = userTableName;
	}
	
	
	private Statement getStatement(){
		try{
			return dbManager.getConnection().createStatement();
		}catch(SQLException e){
			System.out.println(e);
			return null;
		}
	}
	
	/**
		возвращает топ 10
	*/
	public List<String[]> getTop(int max, int days, String dateType){ //+", "+gameTableName
		
		String clearQuery = "DELETE FROM buf_game_results;";
		String insertQuery = "INSERT INTO buf_game_results (userID) SELECT DISTINCT userID FROM "
			+gameTableName+" WHERE SUBDATE(NOW(), INTERVAL "+days+" "+dateType+")<=endTime;";
						
		String fillResultsQuery = "UPDATE buf_game_results SET buf_game_results.results="+
		"(SELECT sum(results) FROM "+gameTableName+" WHERE( buf_game_results.userID=userID)and(SUBDATE(NOW(), INTERVAL "+days+" "+dateType+")<=endTime))"+";";
		
		String fillNamesQuery = "UPDATE buf_game_results, "+userTableName+
			" SET buf_game_results.name="+userTableName+".name, "+
			" buf_game_results.lastName="+userTableName+".lastName "+
			" WHERE buf_game_results.userID="+userTableName+".userID;";
		
		
		String getResultQuery = "SELECT * FROM buf_game_results ORDER BY results DESC LIMIT "+max+";";
		
		try{
			getStatement().executeUpdate(clearQuery);
			getStatement().executeUpdate(insertQuery);
			getStatement().executeUpdate(fillResultsQuery);
			getStatement().executeUpdate(fillNamesQuery);
			
			ResultSet resultSet = getStatement().executeQuery(getResultQuery);
			
			String[] mas = new String[3];
			List<String[]> list = new ArrayList<>();
			
			while(resultSet.next()){
				mas[0] = resultSet.getString("lastName");
				mas[1] = resultSet.getString("name");
				mas[2] = resultSet.getString("results");
				
				list.add(mas.clone());
			}
			return list;
		}catch(SQLException e){
			System.out.println("Error: "+e);
			return null;
		}
	}
	
	
	
	
	public static void main(String args[]){
		DBManager dbManager = new DBManager("anagram", "root", "root");
		DBStatisticDispatcher sd = new DBStatisticDispatcher(dbManager, "games", "users");
		List<String[]> list = sd.getTop(3, 1, "HOUR");
		for(int i=0; i<list.size(); i++){
			System.out.println(list.get(i)[0]+" "+list.get(i)[1]);
		}
		System.out.println("!");

	}
	
}